{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "f72f5edd-7175-4fcc-b5e9-0174216c87d7",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-18T15:58:25.832584Z",
     "iopub.status.busy": "2021-05-18T15:58:25.832303Z",
     "iopub.status.idle": "2021-05-18T15:58:26.145188Z",
     "shell.execute_reply": "2021-05-18T15:58:26.144525Z",
     "shell.execute_reply.started": "2021-05-18T15:58:25.832503Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# 导入pandas库\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "1825d3d1-ab97-49c4-83a1-63c6150c11b5",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-18T15:58:27.395811Z",
     "iopub.status.busy": "2021-05-18T15:58:27.395565Z",
     "iopub.status.idle": "2021-05-18T15:58:28.608270Z",
     "shell.execute_reply": "2021-05-18T15:58:28.607641Z",
     "shell.execute_reply.started": "2021-05-18T15:58:27.395786Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CarID</th>\n",
       "      <th>Stime</th>\n",
       "      <th>Lng</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Status</th>\n",
       "      <th>Speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>22271</td>\n",
       "      <td>22:54:04</td>\n",
       "      <td>114.167000</td>\n",
       "      <td>22.718399</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>22271</td>\n",
       "      <td>18:26:26</td>\n",
       "      <td>114.190598</td>\n",
       "      <td>22.647800</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>22271</td>\n",
       "      <td>18:35:18</td>\n",
       "      <td>114.201401</td>\n",
       "      <td>22.649700</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>22271</td>\n",
       "      <td>16:02:46</td>\n",
       "      <td>114.233498</td>\n",
       "      <td>22.725901</td>\n",
       "      <td>0</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>22271</td>\n",
       "      <td>21:41:17</td>\n",
       "      <td>114.233597</td>\n",
       "      <td>22.720900</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1601302</th>\n",
       "      <td>23873</td>\n",
       "      <td>20:20:03</td>\n",
       "      <td>114.160149</td>\n",
       "      <td>22.606934</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1601303</th>\n",
       "      <td>23873</td>\n",
       "      <td>20:15:13</td>\n",
       "      <td>114.164551</td>\n",
       "      <td>22.605118</td>\n",
       "      <td>1</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1601304</th>\n",
       "      <td>23873</td>\n",
       "      <td>20:16:23</td>\n",
       "      <td>114.168015</td>\n",
       "      <td>22.606083</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1601305</th>\n",
       "      <td>23873</td>\n",
       "      <td>20:16:43</td>\n",
       "      <td>114.168015</td>\n",
       "      <td>22.606083</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1601306</th>\n",
       "      <td>23873</td>\n",
       "      <td>20:26:39</td>\n",
       "      <td>114.170647</td>\n",
       "      <td>22.604383</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1601307 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         CarID     Stime         Lng        Lat  Status  Speed\n",
       "0        22271  22:54:04  114.167000  22.718399       0      0\n",
       "1        22271  18:26:26  114.190598  22.647800       0      4\n",
       "2        22271  18:35:18  114.201401  22.649700       0      0\n",
       "3        22271  16:02:46  114.233498  22.725901       0     24\n",
       "4        22271  21:41:17  114.233597  22.720900       0     19\n",
       "...        ...       ...         ...        ...     ...    ...\n",
       "1601302  23873  20:20:03  114.160149  22.606934       0      0\n",
       "1601303  23873  20:15:13  114.164551  22.605118       1     48\n",
       "1601304  23873  20:16:23  114.168015  22.606083       0      0\n",
       "1601305  23873  20:16:43  114.168015  22.606083       0      0\n",
       "1601306  23873  20:26:39  114.170647  22.604383       0      0\n",
       "\n",
       "[1601307 rows x 6 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 1. 读取Taxi轨迹记录（data/TaxiData）\n",
    "# 读取csv格式的轨迹记录文件\n",
    "data = pd.read_csv(r\"../data/TaxiData\", header=None)\n",
    "# 设置表头\n",
    "data.columns = [\"CarID\", \"Stime\", \"Lng\", \"Lat\", \"Status\", \"Speed\"]\n",
    "# 查看文件内容\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "cb1a842d-c101-4847-bafa-075143ad1007",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-18T16:09:32.037159Z",
     "iopub.status.busy": "2021-05-18T16:09:32.036891Z",
     "iopub.status.idle": "2021-05-18T16:09:33.083021Z",
     "shell.execute_reply": "2021-05-18T16:09:33.082318Z",
     "shell.execute_reply.started": "2021-05-18T16:09:32.037132Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CarID</th>\n",
       "      <th>Stime</th>\n",
       "      <th>Lng</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Status</th>\n",
       "      <th>Speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>22271</td>\n",
       "      <td>00:00:49</td>\n",
       "      <td>114.266502</td>\n",
       "      <td>22.728201</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>397</th>\n",
       "      <td>22271</td>\n",
       "      <td>00:01:48</td>\n",
       "      <td>114.266502</td>\n",
       "      <td>22.728201</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1413</th>\n",
       "      <td>22271</td>\n",
       "      <td>00:02:47</td>\n",
       "      <td>114.266502</td>\n",
       "      <td>22.728201</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>244</th>\n",
       "      <td>22271</td>\n",
       "      <td>00:03:46</td>\n",
       "      <td>114.266502</td>\n",
       "      <td>22.728201</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>247</th>\n",
       "      <td>22271</td>\n",
       "      <td>00:04:45</td>\n",
       "      <td>114.268898</td>\n",
       "      <td>22.729500</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25320</th>\n",
       "      <td>36934</td>\n",
       "      <td>23:49:54</td>\n",
       "      <td>114.056702</td>\n",
       "      <td>22.595800</td>\n",
       "      <td>0</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25831</th>\n",
       "      <td>36934</td>\n",
       "      <td>23:50:42</td>\n",
       "      <td>114.060303</td>\n",
       "      <td>22.603701</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25485</th>\n",
       "      <td>36934</td>\n",
       "      <td>23:52:32</td>\n",
       "      <td>114.069199</td>\n",
       "      <td>22.614500</td>\n",
       "      <td>0</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25749</th>\n",
       "      <td>36934</td>\n",
       "      <td>23:53:02</td>\n",
       "      <td>114.067398</td>\n",
       "      <td>22.618401</td>\n",
       "      <td>0</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26464</th>\n",
       "      <td>36934</td>\n",
       "      <td>23:53:32</td>\n",
       "      <td>114.067299</td>\n",
       "      <td>22.621599</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1598866 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       CarID     Stime         Lng        Lat  Status  Speed\n",
       "39     22271  00:00:49  114.266502  22.728201       0      0\n",
       "397    22271  00:01:48  114.266502  22.728201       0      0\n",
       "1413   22271  00:02:47  114.266502  22.728201       0      0\n",
       "244    22271  00:03:46  114.266502  22.728201       0      0\n",
       "247    22271  00:04:45  114.268898  22.729500       0     11\n",
       "...      ...       ...         ...        ...     ...    ...\n",
       "25320  36934  23:49:54  114.056702  22.595800       0     36\n",
       "25831  36934  23:50:42  114.060303  22.603701       0     40\n",
       "25485  36934  23:52:32  114.069199  22.614500       0     31\n",
       "25749  36934  23:53:02  114.067398  22.618401       0     32\n",
       "26464  36934  23:53:32  114.067299  22.621599       0      7\n",
       "\n",
       "[1598866 rows x 6 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 2. 清洗错误数据\n",
    "# 按CarID和Stime排序\n",
    "data = data.sort_values(by=[\"CarID\", \"Stime\"])\n",
    "# 清洗有错误载客状态的记录\n",
    "data = data[\n",
    "    -(\n",
    "        (data[\"Status\"].shift(-1) == data[\"Status\"].shift())\n",
    "        & (data[\"Status\"].shift(-1) != data[\"Status\"])\n",
    "        & (data[\"CarID\"].shift(-1) == data[\"CarID\"].shift())\n",
    "        & (data[\"CarID\"].shift(-1) == data[\"CarID\"])\n",
    "    )\n",
    "]\n",
    "# 查看文件内容\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "abab79d0-9a68-45b6-a03f-20e1242e794b",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-18T16:11:55.433366Z",
     "iopub.status.busy": "2021-05-18T16:11:55.433085Z",
     "iopub.status.idle": "2021-05-18T16:11:55.477620Z",
     "shell.execute_reply": "2021-05-18T16:11:55.476514Z",
     "shell.execute_reply.started": "2021-05-18T16:11:55.433315Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# 3. 提取OD\n",
    "# 提取Status变化\n",
    "data[\"shiftStatus\"] = data[\"Status\"].shift(-1)\n",
    "data[\"StatusChange\"] = data[\"shiftStatus\"] - data[\"Status\"]\n",
    "od = data[[\"CarID\", \"Stime\", \"Lng\", \"Lat\", \"StatusChange\"]]\n",
    "od = od[\n",
    "    ((od[\"StatusChange\"] == 1) | (od[\"StatusChange\"] == -1))\n",
    "    & (od[\"CarID\"].shift(-1) == od[\"CarID\"])\n",
    "]\n",
    "# 合并OD到同一行\n",
    "od.columns = [\"CarID\", \"Stime\", \"SLng\", \"SLat\", \"StatusChange\"]\n",
    "od[\"ELng\"] = od[\"SLng\"].shift(-1)\n",
    "od[\"ELat\"] = od[\"SLat\"].shift(-1)\n",
    "od[\"Etime\"] = od[\"Stime\"].shift(-1)\n",
    "od = od[(od[\"StatusChange\"] == 1) & (od[\"CarID\"].shift(-1) == od[\"CarID\"])].drop(\n",
    "    \"StatusChange\", axis=1\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "207b4ab2-7ca4-4754-bd30-1ae841b55810",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-18T16:43:49.590657Z",
     "iopub.status.busy": "2021-05-18T16:43:49.590420Z",
     "iopub.status.idle": "2021-05-18T16:43:49.703066Z",
     "shell.execute_reply": "2021-05-18T16:43:49.702514Z",
     "shell.execute_reply.started": "2021-05-18T16:43:49.590629Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# 4. 输出结果\n",
    "od.to_csv(r\"od.csv\", index=None, encoding=\"utf-8_sig\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.5"
  },
  "nteract": {
   "version": "0.28.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
